﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class InvLotDA : IInvLotDA
    {
        private string ALLOCATED_GROSS_WGT = "@ALLOCATED_GROSS_WGT";
        private string ALLOCATED_NET_WGT = "@ALLOCATED_NET_WGT";
        private string ALLOCATED_QTY = "@ALLOCATED_QTY";
        private string ARCHIVE_DATE = "@ARCHIVE_DATE";
        private string ARCHIVE_QTY = "@ARCHIVE_QTY";
        private string CASE_CNT = "@CASE_CNT";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string GROSS_WGT = "@GROSS_WGT";
        private string INNER_PACK = "@INNER_PACK";
        private string LOT = "@LOT";
        private string NET_WGT = "@NET_WGT";
        private string ONHOLD_QTY = "@ONHOLD_QTY";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PALLET = "@PALLET";
        private string PICKED_GROSS_WGT = "@PICKED_GROSS_WGT";
        private string PICKED_NET_WGT = "@PICKED_NET_WGT";
        private string PICKED_QTY = "@PICKED_QTY";
        private string PREALLOCATED_GROSS_WGT = "@PREALLOCATED_GROSS_WGT";
        private string PREALLOCATED_NET_WGT = "@PREALLOCATED_NET_WGT";
        private string PREALLOCATED_QTY = "@PREALLOCATED_QTY";
        private string QTY = "@QTY";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_INV_LOT_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INV_LOT WHERE LOT = @LOT ";
        private string SQL_DELETE_INV_LOT = " DELETE FROM @_@INV_LOT WHERE LOT = @LOT ";
        private string SQL_INSERT_INV_LOT = " INSERT INTO @_@INV_LOT ( WH_ID, LOT, OWNER_ID, SKU_ID, CASE_CNT, INNER_PACK, QTY, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, PREALLOCATED_QTY, ALLOCATED_QTY, PICKED_QTY, ONHOLD_QTY, STATUS, ARCHIVE_QTY, ARCHIVE_DATE, PREALLOCATED_GROSS_WGT, PREALLOCATED_NET_WGT, ALLOCATED_GROSS_WGT, ALLOCATED_NET_WGT, PICKED_GROSS_WGT, PICKED_NET_WGT, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,UNIT_PRICE) VALUES ( @WH_ID, @LOT, @OWNER_ID, @SKU_ID, @CASE_CNT, @INNER_PACK, @QTY, @PALLET, @CUBE, @GROSS_WGT, @NET_WGT, @OTHER_UNIT1, @OTHER_UNIT2, @PREALLOCATED_QTY, @ALLOCATED_QTY, @PICKED_QTY, @ONHOLD_QTY, @STATUS, @ARCHIVE_QTY, @ARCHIVE_DATE, @PREALLOCATED_GROSS_WGT, @PREALLOCATED_NET_WGT, @ALLOCATED_GROSS_WGT, @ALLOCATED_NET_WGT, @PICKED_GROSS_WGT, @PICKED_NET_WGT, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@UNIT_PRICE )  ";
        private string SQL_SELECT_ALL_INV_LOT = " SELECT  ROWID, WH_ID, LOT, OWNER_ID, SKU_ID, CASE_CNT, INNER_PACK, QTY, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, PREALLOCATED_QTY, ALLOCATED_QTY, PICKED_QTY, ONHOLD_QTY, STATUS, ARCHIVE_QTY, ARCHIVE_DATE, PREALLOCATED_GROSS_WGT, PREALLOCATED_NET_WGT, ALLOCATED_GROSS_WGT, ALLOCATED_NET_WGT, PICKED_GROSS_WGT, PICKED_NET_WGT, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,UNIT_PRICE  FROM  @_@INV_LOT ";
        private string SQL_UPDATE_INV_LOT = " UPDATE @_@INV_LOT SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, CASE_CNT = @CASE_CNT, INNER_PACK = @INNER_PACK, QTY = @QTY, PALLET = @PALLET, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, PREALLOCATED_QTY = @PREALLOCATED_QTY, ALLOCATED_QTY = @ALLOCATED_QTY, PICKED_QTY = @PICKED_QTY, ONHOLD_QTY = @ONHOLD_QTY, STATUS = @STATUS, ARCHIVE_QTY = @ARCHIVE_QTY, ARCHIVE_DATE = @ARCHIVE_DATE, PREALLOCATED_GROSS_WGT = @PREALLOCATED_GROSS_WGT, PREALLOCATED_NET_WGT = @PREALLOCATED_NET_WGT, ALLOCATED_GROSS_WGT = @ALLOCATED_GROSS_WGT, ALLOCATED_NET_WGT = @ALLOCATED_NET_WGT, PICKED_GROSS_WGT = @PICKED_GROSS_WGT, PICKED_NET_WGT = @PICKED_NET_WGT, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT  ";
        private string SQL_UPDATE_INV_LOT_ALLOCATED_QTY = " UPDATE @_@INV_LOT SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY ,PREALLOCATED_QTY = PREALLOCATED_QTY - @ALLOCATED_QTY, ALLOCATED_GROSS_WGT = @ALLOCATED_GROSS_WGT, ALLOCATED_NET_WGT = @ALLOCATED_NET_WGT, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_ALLOCATED_QTY_ONLY = "UPDATE @_@INV_LOT SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY , ALLOCATED_GROSS_WGT = @ALLOCATED_GROSS_WGT, ALLOCATED_NET_WGT = @ALLOCATED_NET_WGT, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOC = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + ( SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOC_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND  LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), @_@INV_LOT.ONHOLD_QTY = @_@INV_LOT.ONHOLD_QTY + ( SELECT  ISNULL(SUM(@_@INV_LOT_LOC_LPN.QTY),0) FROM @_@INV_LOT_LOC_LPN WHERE @_@INV_LOT_LOC_LPN.LOT = @LOT), @_@INV_LOT.STATUS = (CASE @_@INV_LOT.QTY WHEN @_@INV_LOT.ONHOLD_QTY THEN 'OK' ELSE 'HOLD' END) WHERE @_@INV_LOT.LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LOC = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT  = @_@INV_LOT.LOT AND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE  LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LOC_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND  LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE  LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE'\tAND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_OWNER = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT AND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 AND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\tAND ILLL.OWNER_ID = @OWNER_ID ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 AND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID \tAND ILLL.OWNER_ID = @OWNER_ID ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_SKU = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY + (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT AND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 AND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.SKU_ID = ILLL.SKU_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\t\tAND ILLL.OWNER_ID = @OWNER_ID\tAND @_@INV_LOT.SKU_ID = @SKU_ID\tAND ILLL.SKU_ID = @SKU_ID ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 AND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.SKU_ID = ILLL.SKU_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\t\tAND ILLL.OWNER_ID = @OWNER_ID\tAND @_@INV_LOT.SKU_ID = @SKU_ID\tAND ILLL.SKU_ID = @SKU_ID ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOC = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - ( SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOC_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), @_@INV_LOT.ONHOLD_QTY = @_@INV_LOT.ONHOLD_QTY - ( SELECT ISNULL(SUM(@_@INV_LOT_LOC_LPN.QTY),0) FROM @_@INV_LOT_LOC_LPN WHERE @_@INV_LOT_LOC_LPN.LOT = @LOT), @_@INV_LOT.STATUS = (CASE @_@INV_LOT.QTY WHEN @_@INV_LOT.ONHOLD_QTY THEN 'OK' ELSE 'HOLD' END) WHERE @_@INV_LOT.LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LOC = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT  = @_@INV_LOT.LOT AND LOC.LOC_FLAG <> 'DAMAGE' \tAND  ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LOC.LOC = @LOC \tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LOC_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND LOC.LOC = @LOC\tAND ILLL.LOC = @LOC\tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN \tAND @_@INV_LOT.LOT = @LOT\tAND ILLL.LOT = @LOT ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LPN = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN  ON ILLL.LPN_ID = ILLL.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT \tAND LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \tAND LPN.LPN_ID = @LPN \tAND ILLL.LPN_ID = @LPN ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END)  ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_OWNER = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT AND LOC.LOC_FLAG <> 'DAMAGE' \tAND ILLL.QTY > 0 \t\tAND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\tAND ILLL.OWNER_ID = @OWNER_ID ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \t\tAND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID \tAND ILLL.OWNER_ID = @OWNER_ID ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_SKU = " UPDATE @_@INV_LOT SET @_@INV_LOT.UPDATED_BY = @UPDATED_BY, @_@INV_LOT.UPDATED_DATE = GETDATE(), ONHOLD_QTY = ONHOLD_QTY - (SELECT ISNULL(SUM(ILLL.QTY),0) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID WHERE ILLL.LOT = @_@INV_LOT.LOT AND\t LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 \t\tAND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.SKU_ID = ILLL.SKU_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\t\tAND ILLL.OWNER_ID = @OWNER_ID\tAND @_@INV_LOT.SKU_ID = @SKU_ID\tAND ILLL.SKU_ID = @SKU_ID ) FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION LOC ON ILLL.LOC = LOC.LOC LEFT JOIN @_@LPN LPN ON ILLL.LPN_ID = LPN.LPN_ID LEFT JOIN @_@INV_LOT ON ILLL.LOT = @_@INV_LOT.LOT WHERE LOC.LOC_FLAG <> 'DAMAGE' AND ILLL.QTY > 0 AND @_@INV_LOT.OWNER_ID = ILLL.OWNER_ID\tAND @_@INV_LOT.SKU_ID = ILLL.SKU_ID\tAND @_@INV_LOT.OWNER_ID = @OWNER_ID\t\tAND ILLL.OWNER_ID = @OWNER_ID\tAND @_@INV_LOT.SKU_ID = @SKU_ID\tAND ILLL.SKU_ID = @SKU_ID ; UPDATE @_@INV_LOT SET STATUS = (CASE QTY WHEN 0 THEN @STATUS WHEN ONHOLD_QTY THEN 'HOLD' ELSE 'OK' END) ";
        private string SQL_UPDATE_INV_LOT_PICKED_QTY = " UPDATE @_@INV_LOT SET PICKED_QTY = PICKED_QTY + @PICKED_QTY , ALLOCATED_QTY = ALLOCATED_QTY - @PICKED_QTY, PICKED_GROSS_WGT = @PICKED_GROSS_WGT, PICKED_NET_WGT = @PICKED_NET_WGT, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT  ";
        private string SQL_UPDATE_INV_LOT_PICKED_QTY_ONLY = "UPDATE @_@INV_LOT SET PICKED_QTY = PICKED_QTY + @PICKED_QTY , UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_PREALLOCATED_QTY = " UPDATE @_@INV_LOT SET PREALLOCATED_QTY = PREALLOCATED_QTY + @PREALLOCATED_QTY ,PREALLOCATED_GROSS_WGT = PREALLOCATED_GROSS_WGT + @PREALLOCATED_GROSS_WGT,PREALLOCATED_NET_WGT = PREALLOCATED_NET_WGT + @PREALLOCATED_NET_WGT, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_QTY = " UPDATE @_@INV_LOT SET QTY = @QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_REVERT_ALLOCATED_QTY = " UPDATE @_@INV_LOT SET ALLOCATED_QTY = ALLOCATED_QTY - @ALLOCATED_QTY ,PREALLOCATED_QTY = PREALLOCATED_QTY + @ALLOCATED_QTY,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_SHIPPED_QTY = " UPDATE @_@INV_LOT SET QTY = QTY - @SHIPPED_QTY, PICKED_QTY = PICKED_QTY - @SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT  ";
        private string SQL_UPDATE_INV_LOT_STATUS = " UPDATE @_@INV_LOT SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_UN_SHIPPED_QTY = " UPDATE @_@INV_LOT SET QTY = QTY + @SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_UN_SHIPPED_QTY_TO_PICKED = " UPDATE @_@INV_LOT SET QTY = QTY + @SHIPPED_QTY,PICKED_QTY=PICKED_QTY+@SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_UNIT_PRICE = " UPDATE @_@INV_LOT SET UNIT_PRICE=@UNIT_PRICE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT  ";
        private string SQL_UPDATE_INV_LOT_XSHIPPED_QTY = " UPDATE @_@INV_LOT SET QTY = QTY - @SHIPPED_QTY, ALLOCATED_QTY=ALLOCATED_QTY-@SHIPPED_QTY,  UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckInvLotIDUnique(string invLotID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", invLotID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INV_LOT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckInvLotIDUnique(DataBase dataBase, DbTransaction tran, string invLotID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", invLotID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INV_LOT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int ExecuteInvLotHold(DataBase dataBase, DbTransaction tran, string status, string holdType, string lot, string loc, string lpn, string ownerID, string skuID, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (holdType.ToUpper())
                {
                    case "LOT":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            break;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT.Replace("@_@", whLoginID);
                        goto Label_0129;

                    case "LOC":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_0189;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOC.Replace("@_@", whLoginID);
                        goto Label_019E;

                    case "LPN":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_020D;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LPN.Replace("@_@", whLoginID);
                        goto Label_0222;

                    case "LOT_LOC":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_0291;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LOC.Replace("@_@", whLoginID);
                        goto Label_02A6;

                    case "LOT_LPN":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_0325;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LPN.Replace("@_@", whLoginID);
                        goto Label_033A;

                    case "LOC_LPN":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_03B9;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOC_LPN.Replace("@_@", whLoginID);
                        goto Label_03CE;

                    case "LOT_LOC_LPN":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_044D;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_LOT_LOC_LPN.Replace("@_@", whLoginID);
                        goto Label_0462;

                    case "OWNER":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_04F1;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_OWNER.Replace("@_@", whLoginID);
                        goto Label_0506;

                    case "SKU":
                        if (!(status.ToUpper() == "HOLD"))
                        {
                            goto Label_0575;
                        }
                        sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_HOLD_BY_SKU.Replace("@_@", whLoginID);
                        goto Label_058A;

                    default:
                        goto Label_05D6;
                }
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT.Replace("@_@", whLoginID);
            Label_0129:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOT", lot) };
                goto Label_05D6;
            Label_0189:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOC.Replace("@_@", whLoginID);
            Label_019E:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOC", loc), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_020D:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LPN.Replace("@_@", whLoginID);
            Label_0222:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LPN", lpn), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_0291:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LOC.Replace("@_@", whLoginID);
            Label_02A6:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_0325:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LPN.Replace("@_@", whLoginID);
            Label_033A:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOT", lot), new SqlParameter("@LPN", lpn), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_03B9:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOC_LPN.Replace("@_@", whLoginID);
            Label_03CE:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOC", loc), new SqlParameter("@LPN", lpn), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_044D:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_LOT_LOC_LPN.Replace("@_@", whLoginID);
            Label_0462:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@LPN", lpn), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_04F1:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_OWNER.Replace("@_@", whLoginID);
            Label_0506:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@STATUS", status) };
                goto Label_05D6;
            Label_0575:
                sql = this.SQL_UPDATE_INV_LOT_HOLD_TO_OK_BY_SKU.Replace("@_@", whLoginID);
            Label_058A:;
                paras = new SqlParameter[] { new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@STATUS", status) };
            Label_05D6:
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public InvLotInfo GetInvLotByID(string lotID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_LOT.Replace("@_@", whLoginID) + " WHERE LOT = @LOT  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotID) };
            InvLotInfo invLotInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    invLotInfo = new InvLotInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["ONHOLD_QTY"].ToString() != "") ? decimal.Parse(reader["ONHOLD_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["PREALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PREALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["PICKED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PICKED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_NET_WGT"].ToString())) : ((double) 0f), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f);
                }
            }
            return invLotInfo;
        }

        public InvLotInfo GetInvLotByID(DataBase dataBase, DbTransaction tran, string lotID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_LOT.Replace("@_@", whLoginID) + " WHERE LOT = @LOT  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotID) };
            InvLotInfo invLotInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                invLotInfo = new InvLotInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["ONHOLD_QTY"].ToString() != "") ? decimal.Parse(reader["ONHOLD_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["PREALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PREALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["PICKED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PICKED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_NET_WGT"].ToString())) : ((double) 0f), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invLotInfo;
        }

        public int InsertInvLot(InvLotInfo invLotInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLot_Parameters(invLotInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_INSERT_INV_LOT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertInvLot(DataBase dataBase, DbTransaction tran, InvLotInfo invLotInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLot_Parameters(invLotInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_LOT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_InvLot_Parameters(InvLotInfo invLotInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.INNER_PACK, SqlDbType.Decimal, 13), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Float, 8), new SqlParameter(this.GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT1, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT2, SqlDbType.Float, 8), new SqlParameter(this.PREALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKED_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.ONHOLD_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.ARCHIVE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ARCHIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.PREALLOCATED_GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.PREALLOCATED_NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.ALLOCATED_GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.ALLOCATED_NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.PICKED_GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.PICKED_NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float)
             };
            if (!string.IsNullOrEmpty(invLotInfo.WhID))
            {
                paramArray[0].Value = invLotInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = invLotInfo.Lot;
            paramArray[2].Value = invLotInfo.OwnerID;
            paramArray[3].Value = invLotInfo.SkuID;
            paramArray[4].Value = invLotInfo.CaseCnt;
            paramArray[5].Value = invLotInfo.InnerPack;
            paramArray[6].Value = invLotInfo.Qty;
            paramArray[7].Value = invLotInfo.Pallet;
            paramArray[8].Value = invLotInfo.Cube;
            paramArray[9].Value = invLotInfo.GrossWgt;
            paramArray[10].Value = invLotInfo.NetWgt;
            paramArray[11].Value = invLotInfo.OtherUnit1;
            paramArray[12].Value = invLotInfo.OtherUnit2;
            paramArray[13].Value = invLotInfo.PreallocatedQty;
            paramArray[14].Value = invLotInfo.AllocatedQty;
            paramArray[15].Value = invLotInfo.PickedQty;
            paramArray[0x10].Value = invLotInfo.OnholdQty;
            paramArray[0x11].Value = invLotInfo.Status.ToString();
            paramArray[0x12].Value = invLotInfo.ArchiveQty;
            paramArray[0x13].Value = invLotInfo.ArchiveDate;
            paramArray[20].Value = invLotInfo.PreallocatedGrossWgt;
            paramArray[0x15].Value = invLotInfo.PreallocatedNetWgt;
            paramArray[0x16].Value = invLotInfo.AllocatedGrossWgt;
            paramArray[0x17].Value = invLotInfo.AllocatedNetWgt;
            paramArray[0x18].Value = invLotInfo.PickedGrossWgt;
            paramArray[0x19].Value = invLotInfo.PickedNetWgt;
            paramArray[0x1a].Value = invLotInfo.CreatedBy;
            paramArray[0x1b].Value = invLotInfo.UpdatedBy;
            paramArray[0x1c].Value = invLotInfo.UnitPrice;
            return paramArray;
        }

        public int UpdateInvLot(InvLotInfo invLotInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLot_Parameters(invLotInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPDATE_INV_LOT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLot(DataBase dataBase, DbTransaction tran, InvLotInfo invLotInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLot_Parameters(invLotInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_LOT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLot(DataBase dataBase, DbTransaction tran, InvLotInfo invLotInfo, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (updateType)
                {
                    case "Qty":
                        sql = this.SQL_UPDATE_INV_LOT_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@QTY", invLotInfo.Qty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "Status":
                        sql = this.SQL_UPDATE_INV_LOT_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", invLotInfo.Status), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "PreAllocatedQtyWgt":
                        sql = this.SQL_UPDATE_INV_LOT_PREALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PREALLOCATED_QTY", invLotInfo.PreallocatedQty), new SqlParameter("@PREALLOCATED_GROSS_WGT", invLotInfo.PreallocatedGrossWgt), new SqlParameter("@PREALLOCATED_NET_WGT", invLotInfo.PreallocatedNetWgt), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "AllocatedQtyWgtOnly":
                        sql = this.SQL_UPDATE_INV_LOT_ALLOCATED_QTY_ONLY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invLotInfo.AllocatedQty), new SqlParameter("@ALLOCATED_GROSS_WGT", invLotInfo.AllocatedGrossWgt), new SqlParameter("@ALLOCATED_NET_WGT", invLotInfo.AllocatedNetWgt), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "AllocatedQtyWgt":
                        sql = this.SQL_UPDATE_INV_LOT_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invLotInfo.AllocatedQty), new SqlParameter("@ALLOCATED_GROSS_WGT", invLotInfo.AllocatedGrossWgt), new SqlParameter("@ALLOCATED_NET_WGT", invLotInfo.AllocatedNetWgt), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "RevertAllocatedQty":
                        sql = this.SQL_UPDATE_INV_LOT_REVERT_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invLotInfo.AllocatedQty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "PickedQtyWgt":
                        sql = this.SQL_UPDATE_INV_LOT_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invLotInfo.PickedQty), new SqlParameter("@PICKED_GROSS_WGT", invLotInfo.PickedGrossWgt), new SqlParameter("@PICKED_NET_WGT", invLotInfo.PickedNetWgt), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "ShippedQty":
                        sql = this.SQL_UPDATE_INV_LOT_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotInfo.Qty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "PickedQtyOnly":
                        sql = this.SQL_UPDATE_INV_LOT_PICKED_QTY_ONLY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invLotInfo.PickedQty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "UnShippedQty":
                        sql = this.SQL_UPDATE_INV_LOT_UN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotInfo.Qty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "UnShippedQtyToPicked":
                        sql = this.SQL_UPDATE_INV_LOT_UN_SHIPPED_QTY_TO_PICKED.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotInfo.Qty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;

                    case "XShippedQty":
                        sql = this.SQL_UPDATE_INV_LOT_XSHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotInfo.Qty), new SqlParameter("@UPDATED_BY", invLotInfo.UpdatedBy), new SqlParameter("@LOT", invLotInfo.Lot) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLotUnitPriceBylot(DataBase dataBase, DbTransaction tran, string lotID, float price, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotID), new SqlParameter("@UNIT_PRICE", price), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_LOT_UNIT_PRICE.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

